package blackberryAppBus;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Hashtable;

import blackberryApp.BBApp_cjbBBApp;
import blackberryAppCom.BBApp_Common;
import blackberryAppCom.BBApp_Exception;

public class BBApp_MobilBusiness {
	private String lStrSchema=BBApp_Common.fnGetProperty("DB_SCHEMA");	
	private String lStrMbuSchema=BBApp_Common.fnGetProperty("DB_SCHEMA_MBU");
	
	/**
	 * fnRetStatusKlaim
	 */
	public Hashtable fnRetStatusKlaim(BBApp_cjbBBApp lObjBBApp_cjbBBApp) throws BBApp_Exception {
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		ResultSetMetaData rsmd 		  = null;
		
		Hashtable htResult			  = new Hashtable();
		Hashtable lHstDataClaim 	  = new Hashtable();
		
		String strNoPlat		= "";
		String strNoPolis		= "";
		String strRecNo			= "";
		
		BBApp_Common bbCommon = new BBApp_Common();
		
		if(lObjBBApp_cjbBBApp == null)
				throw new BBApp_Exception("Cjb Value is null","0000");
		else {
				strNoPlat		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPlat());
				strNoPolis		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPolis());
				strRecNo		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrRecNo());
		}
		
		BBApp_cjbBBApp lObjBBApp_cjbBBAppResult = new BBApp_cjbBBApp();
				
		try {
			lCnFetch = new BBApp_Common().OpenConnection(2);
			if(lCnFetch == null) //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
			
			lSBquery.append("select * from ( ");
			lSBquery.append("select zz.*, rownum row_num from ( ");
			lSBquery.append("select a.mnk_no_klaim as no_klaim, ");
			lSBquery.append("a.mkl_tgl_input as tgl_register, ");
			lSBquery.append("a.mkl_tgl_klaim as tgl_lapor, ");
			lSBquery.append("NVL(b.mks_no_plat,'tidak ada data') as no_plat, ");
			lSBquery.append("NVL(d.lbe_nm_bengkel,'tidak ada data') as bengkel, ");
			lSBquery.append("NVL((select f.LKOK_NOTE  ");
			lSBquery.append("from mst_follow_up_klaim e, ");
			lSBquery.append("lst_kondisi_klaim f ");
			lSBquery.append("where e.mkl_no_spk = a.mkl_no_spk and ");
			lSBquery.append("f.LKOK_ID = e.KONDISI and ");
			lSBquery.append("e.STS_AKTIF = '1'),'Tidak ada data') as kondisi, ");
			lSBquery.append("NVL((select h.LKEK_NOTE ");
			lSBquery.append("from mst_follow_up_klaim g, ");
			lSBquery.append("lst_kendala_klaim h ");
			lSBquery.append("where g.mkl_no_spk = a.mkl_no_spk and ");
			lSBquery.append("h.LKEK_ID = g.kendala and ");
			lSBquery.append("g.STS_AKTIF = '1'),'Tidak ada data') as kendala, ");
			lSBquery.append("NVL((select i.keterangan ");
			lSBquery.append("from mst_follow_up_klaim i ");
			lSBquery.append("where i.mkl_no_spk = a.mkl_no_spk and ");
			lSBquery.append("i.sts_aktif = '1'),'Tidak ada data') as keterangan, ");
			lSBquery.append("(select lsl.lsl_note ");
			lSBquery.append("from lst_sts_klaim lsl ");
			lSBquery.append("where lsl.lsl_id = a.lsl_id) as status_klaim "); 
			lSBquery.append("from mst_klaim a, ");
			lSBquery.append("mst_kend_survey b, ");
			lSBquery.append("lst_bengkel d ");
			lSBquery.append("where b.mkl_no_spk(+) = a.mkl_no_spk and ");
			lSBquery.append("d.lbe_id(+) = b.lbe_id and ");
			lSBquery.append("a.lsl_id not in ('04','99') ");
			
			if (bbCommon.fnCutSpace(strNoPlat).length() > 0) {
				lSBquery.append("and trim(upper(b.mks_no_plat)) like trim(upper('"+bbCommon.fnCutSpace(strNoPlat)+"')) ");
			}
			
//			if (bbCommon.fnCutSpace(strNoPolis).length() > 0) {
//				lSBquery.append("and a.mkl_no_polis = '"+bbCommon.fnCutSpace(strNoPolis)+"' ");
//			}
			
			lSBquery.append("order by tgl_lapor desc");		
			lSBquery.append(") zz ");
			lSBquery.append(") ");
					
			BBApp_Common.fnPrint("[BBApp_MobilBusiness] fnRetStatusKlaim: " + lSBquery.toString());
			BBApp_Common.fnWriteLog("BBApp_cMobilScreen","fnRetStatusKlaim: ",lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();	
			
			rsmd = lRsFetch.getMetaData();
			int intCols = rsmd.getColumnCount();
			int i = 1;
			
			while(lRsFetch.next()) {
				String[] strData = new String[intCols];
				for (int a=1; a<=intCols; a++) {
					strData[a-1] = BBApp_Common.fnGetValue(lRsFetch.getString(a));
				}
				//htResult.put(String.valueOf(i), strData);
				htResult.put(BBApp_Common.fnGetValue(lRsFetch.getString("row_num")), strData);
				i = i + 1;
			}
			
			if (htResult.size() > 0) {
				if (strRecNo.trim().length() == 0) {
					strRecNo = "1";
				}
				
				Hashtable htFinal = new Hashtable();
				String[] strFinal = (String[]) htResult.get(strRecNo);
				htFinal.put("1", strFinal);
				
				lHstDataClaim.put("Register", htFinal);
				
				lHstDataClaim.put("RecNo", strRecNo);
//				BBApp_Common.fnWriteLog("BBApp_cMobilScreen","fnRetStatusKlaim: ",">> RecNo: " + strRecNo);
				
				lHstDataClaim.put("MaxNo", String.valueOf(htResult.size()));
//				BBApp_Common.fnWriteLog("BBApp_cMobilScreen","fnRetStatusKlaim: ",">> MaxNo: " + htResult.size());
			}
			
		}
		catch(Exception e)
		{
			//e.printStackTrace();
			BBApp_Common.fnWriteLog("BBApp_MobilBusiness", "BBApp_MobilBusiness(fnRetStatusKlaim) : ","Query Exception: " + e.getMessage());
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("BBApp_MobilBusiness", "BBApp_MobilBusiness(fnRetStatusKlaimPlat : ","Close Exception : "+e);
			}		
		}
		return lHstDataClaim;
	}
	
	/**
	 * fnRetDetPesertaPolis
	 */
	public BBApp_cjbBBApp fnRetDetPesertaPolis(BBApp_cjbBBApp lObjBBApp_cjbBBApp) throws BBApp_Exception {
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		ResultSetMetaData rsmd 		  = null;
		
		String strNoPolis		= "";
		String strNama			= "";
		String strNoPlat		= "";
		String strNoRangka		= "";
		String strNoMesin		= "";
		
		if(lObjBBApp_cjbBBApp == null)
				throw new BBApp_Exception("Cjb Value is null","0000");
		else {
				strNoPolis		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPolis());
				strNama			= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNama());
				strNoPlat		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPlat());
				strNoRangka		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoRangka());
				strNoMesin		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoMesin());
		}
		
		BBApp_cjbBBApp lObjBBApp_cjbBBAppResult = new BBApp_cjbBBApp();
				
		try {
			lCnFetch = new BBApp_Common().OpenConnection(2);
			if(lCnFetch == null) //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
				
			lSBquery.append("	select a.mds_no_polis, ");
			lSBquery.append("       (select mc.mcl_name from mst_client mc where mc.mcl_id = c.client_id) as nama_client, ");
			lSBquery.append("       b.mke_no_plat, ");
			lSBquery.append("       b.mke_no_rangka, ");
			lSBquery.append("       b.mke_no_mesin ");
			lSBquery.append("  from mst_det_Sales a, mst_kendaraan b, mst_sales c ");
			lSBquery.append(" where b.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and c.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and a.med_no_endors is null ");
			lSBquery.append("   and a.mds_no_polis = '"+strNoPolis+"' ");			
					
			//BBApp_Common.fnPrint("[BBApp_Business] fnRetrieveDetailPeserta: " + lSBquery.toString());
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaPolis) : "," " + lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();	
			
			while(lRsFetch.next()) {
				lObjBBApp_cjbBBAppResult.setStrNoPolis(BBApp_Common.fnGetValue(lRsFetch.getString("mds_no_polis")));
				lObjBBApp_cjbBBAppResult.setStrNama(BBApp_Common.fnGetValue(lRsFetch.getString("nama_client")));
				lObjBBApp_cjbBBAppResult.setStrNoPlat(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_plat")));
				lObjBBApp_cjbBBAppResult.setStrNoRangka(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_rangka")));
				lObjBBApp_cjbBBAppResult.setStrNoMesin(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_mesin")));
			}
			
			//lObjBBApp_cjbBBAppResult.setStrNoKartu(strNoKartu);
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaPolis) : ","Close Exception : "+e);
			}		
		}
		return lObjBBApp_cjbBBAppResult;
	}
	
	/**
	 * fnRetDetPesertaRangka
	 */
	public BBApp_cjbBBApp fnRetDetPesertaRangka(BBApp_cjbBBApp lObjBBApp_cjbBBApp) throws BBApp_Exception {
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		ResultSetMetaData rsmd 		  = null;
		
		String strNoPolis		= "";
		String strNama			= "";
		String strNoPlat		= "";
		String strNoRangka		= "";
		String strNoMesin		= "";
		
		if(lObjBBApp_cjbBBApp == null)
				throw new BBApp_Exception("Cjb Value is null","0000");
		else {
				strNoPolis		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPolis());
				strNama			= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNama());
				strNoPlat		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPlat());
				strNoRangka		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoRangka());
				strNoMesin		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoMesin());
		}
		
		BBApp_cjbBBApp lObjBBApp_cjbBBAppResult = new BBApp_cjbBBApp();
				
		try {
			lCnFetch = new BBApp_Common().OpenConnection(2);
			if(lCnFetch == null) //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
				
			lSBquery.append("	select a.mds_no_polis, ");
			lSBquery.append("       (select mc.mcl_name from mst_client mc where mc.mcl_id = c.client_id) as nama_client, ");
			lSBquery.append("       b.mke_no_plat, ");
			lSBquery.append("       b.mke_no_rangka, ");
			lSBquery.append("       b.mke_no_mesin ");
			lSBquery.append("  from mst_det_Sales a, mst_kendaraan b, mst_sales c ");
			lSBquery.append(" where b.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and c.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and a.med_no_endors is null ");
			lSBquery.append("   and a.mds_no_polis||a.mds_prod_ke = (select max(mds.mds_no_polis||max(mds.mds_prod_ke)) ");
			lSBquery.append("   									 from mst_Det_Sales mds ");
			lSBquery.append("   									 where mds.msp_no_spak = b.msp_no_spak and ");
			lSBquery.append("        									   mds.med_no_endors is null ");
			lSBquery.append("   									 group by mds.mds_no_polis) ");
			lSBquery.append("   and trim(upper(b.mke_no_rangka)) = upper('"+strNoRangka+"') ");			
					
			//BBApp_Common.fnPrint("[BBApp_Business] fnRetrieveDetailPeserta: " + lSBquery.toString());
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaRangka) : "," " + lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();	
			
			while(lRsFetch.next()) {
				lObjBBApp_cjbBBAppResult.setStrNoPolis(BBApp_Common.fnGetValue(lRsFetch.getString("mds_no_polis")));
				lObjBBApp_cjbBBAppResult.setStrNama(BBApp_Common.fnGetValue(lRsFetch.getString("nama_client")));
				lObjBBApp_cjbBBAppResult.setStrNoPlat(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_plat")));
				lObjBBApp_cjbBBAppResult.setStrNoRangka(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_rangka")));
				lObjBBApp_cjbBBAppResult.setStrNoMesin(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_mesin")));
			}
			
			//lObjBBApp_cjbBBAppResult.setStrNoKartu(strNoKartu);
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaRangka) : ","Close Exception : "+e);
			}		
		}
		return lObjBBApp_cjbBBAppResult;
	}
	
	/**
	 * fnRetDetPesertaMesin
	 */
	public BBApp_cjbBBApp fnRetDetPesertaMesin(BBApp_cjbBBApp lObjBBApp_cjbBBApp) throws BBApp_Exception {
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		ResultSetMetaData rsmd 		  = null;
		
		String strNoPolis		= "";
		String strNama			= "";
		String strNoPlat		= "";
		String strNoRangka		= "";
		String strNoMesin		= "";
		
		if(lObjBBApp_cjbBBApp == null)
				throw new BBApp_Exception("Cjb Value is null","0000");
		else {
				strNoPolis		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPolis());
				strNama			= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNama());
				strNoPlat		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPlat());
				strNoRangka		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoRangka());
				strNoMesin		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoMesin());
		}
		
		BBApp_cjbBBApp lObjBBApp_cjbBBAppResult = new BBApp_cjbBBApp();
				
		try {
			lCnFetch = new BBApp_Common().OpenConnection(2);
			if(lCnFetch == null) //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
				
			lSBquery.append("	select a.mds_no_polis, ");
			lSBquery.append("       (select mc.mcl_name from mst_client mc where mc.mcl_id = c.client_id) as nama_client, ");
			lSBquery.append("       b.mke_no_plat, ");
			lSBquery.append("       b.mke_no_rangka, ");
			lSBquery.append("       b.mke_no_mesin ");
			lSBquery.append("  from mst_det_Sales a, mst_kendaraan b, mst_sales c ");
			lSBquery.append(" where b.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and c.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and a.med_no_endors is null ");
			lSBquery.append("   and a.mds_no_polis||a.mds_prod_ke = (select max(mds.mds_no_polis||max(mds.mds_prod_ke)) ");
			lSBquery.append("   									 from mst_Det_Sales mds ");
			lSBquery.append("   									 where mds.msp_no_spak = b.msp_no_spak and ");
			lSBquery.append("        									   mds.med_no_endors is null ");
			lSBquery.append("   									 group by mds.mds_no_polis) ");
			lSBquery.append("   and trim(upper(b.mke_no_mesin)) = upper('"+strNoMesin+"') ");			
					
			//BBApp_Common.fnPrint("[BBApp_Business] fnRetrieveDetailPeserta: " + lSBquery.toString());
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaMesin) : "," " + lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();	
			
			while(lRsFetch.next()) {
				lObjBBApp_cjbBBAppResult.setStrNoPolis(BBApp_Common.fnGetValue(lRsFetch.getString("mds_no_polis")));
				lObjBBApp_cjbBBAppResult.setStrNama(BBApp_Common.fnGetValue(lRsFetch.getString("nama_client")));
				lObjBBApp_cjbBBAppResult.setStrNoPlat(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_plat")));
				lObjBBApp_cjbBBAppResult.setStrNoRangka(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_rangka")));
				lObjBBApp_cjbBBAppResult.setStrNoMesin(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_mesin")));
			}
			
			//lObjBBApp_cjbBBAppResult.setStrNoKartu(strNoKartu);
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaMesin) : ","Close Exception : "+e);
			}		
		}
		return lObjBBApp_cjbBBAppResult;
	}
	
	/**
	 * fnRetDetPesertaPlat
	 */
	public BBApp_cjbBBApp fnRetDetPesertaPlat(BBApp_cjbBBApp lObjBBApp_cjbBBApp) throws BBApp_Exception {
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		ResultSetMetaData rsmd 		  = null;
		
		String strNoPolis		= "";
		String strNama			= "";
		String strNoPlat		= "";
		String strNoRangka		= "";
		String strNoMesin		= "";
		
		if(lObjBBApp_cjbBBApp == null)
				throw new BBApp_Exception("Cjb Value is null","0000");
		else {
				strNoPolis		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPolis());
				strNama			= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNama());
				strNoPlat		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPlat());
				strNoRangka		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoRangka());
				strNoMesin		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoMesin());
		}
		
		BBApp_cjbBBApp lObjBBApp_cjbBBAppResult = new BBApp_cjbBBApp();
				
		try {
			lCnFetch = new BBApp_Common().OpenConnection(2);
			if(lCnFetch == null) //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
				
			lSBquery.append("	select a.mds_no_polis, ");
			lSBquery.append("       (select mc.mcl_name from mst_client mc where mc.mcl_id = c.client_id) as nama_client, ");
			lSBquery.append("       b.mke_no_plat, ");
			lSBquery.append("       b.mke_no_rangka, ");
			lSBquery.append("       b.mke_no_mesin ");
			lSBquery.append("  from mst_det_Sales a, mst_kendaraan b, mst_sales c ");
			lSBquery.append(" where b.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and c.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("   and a.med_no_endors is null ");
			lSBquery.append("   and a.mds_no_polis||a.mds_prod_ke = (select max(mds.mds_no_polis||max(mds.mds_prod_ke)) ");
			lSBquery.append("   									 from mst_Det_Sales mds ");
			lSBquery.append("   									 where mds.msp_no_spak = b.msp_no_spak and ");
			lSBquery.append("        									   mds.med_no_endors is null ");
			lSBquery.append("   									 group by mds.mds_no_polis) ");
			lSBquery.append("   and trim(upper(b.mke_no_plat)) = upper('"+strNoPlat+"') ");			
					
			//BBApp_Common.fnPrint("[BBApp_Business] fnRetrieveDetailPeserta: " + lSBquery.toString());
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaPlat) : "," " + lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();	
			
			while(lRsFetch.next()) {
				lObjBBApp_cjbBBAppResult.setStrNoPolis(BBApp_Common.fnGetValue(lRsFetch.getString("mds_no_polis")));
				lObjBBApp_cjbBBAppResult.setStrNama(BBApp_Common.fnGetValue(lRsFetch.getString("nama_client")));
				lObjBBApp_cjbBBAppResult.setStrNoPlat(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_plat")));
				lObjBBApp_cjbBBAppResult.setStrNoRangka(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_rangka")));
				lObjBBApp_cjbBBAppResult.setStrNoMesin(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_mesin")));
			}
			
			//lObjBBApp_cjbBBAppResult.setStrNoKartu(strNoKartu);
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaPlat) : ","Close Exception : "+e);
			}		
		}
		return lObjBBApp_cjbBBAppResult;
	}
	
	/**
	 * fnRetDetPesertaPlatRangkaMesin
	 */
	public BBApp_cjbBBApp fnRetDetPesertaPlatRangkaMesin(BBApp_cjbBBApp lObjBBApp_cjbBBApp) throws BBApp_Exception {
		Connection lCnFetch		      = null;	
		PreparedStatement lPStmtFetch = null;		
		ResultSet lRsFetch		      = null;
		StringBuffer lSBquery 		  = new StringBuffer();
		ResultSetMetaData rsmd 		  = null;
		
		String strNoPolis		= "";
		String strNama			= "";
		String strNoPlat		= "";
		String strNoRangka		= "";
		String strNoMesin		= "";
		
		if(lObjBBApp_cjbBBApp == null)
				throw new BBApp_Exception("Cjb Value is null","0000");
		else {
				strNoPolis		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPolis());
				strNama			= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNama());
				strNoPlat		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoPlat());
				strNoRangka		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoRangka());
				strNoMesin		= BBApp_Common.fnGetValue(lObjBBApp_cjbBBApp.getStrNoMesin());
		}
		
		BBApp_cjbBBApp lObjBBApp_cjbBBAppResult = new BBApp_cjbBBApp();
				
		try {
			lCnFetch = new BBApp_Common().OpenConnection(2);
			if(lCnFetch == null) //if connection is not established
				throw new BBApp_Exception("NoDataBase Connection","1500");
				
			lSBquery.append("select (select max(mds_no_polis) ");
			lSBquery.append("		from mst_det_sales x ");
			lSBquery.append("        where x.msp_no_spak = a.msp_no_spak ");
			lSBquery.append("        and x.mds_prod_ke = (select max(mds_prod_ke) ");
			lSBquery.append("                             from mst_det_sales o ");
			lSBquery.append("                             where o.msp_no_spak = x.msp_no_spak)) mds_no_polis, ");
			lSBquery.append("       (select y.mcl_name ");
			lSBquery.append("        from mst_spak x, mst_client y ");
			lSBquery.append("        where x.client_id = y.mcl_id ");
			lSBquery.append("        and x.msp_no_spak = a.msp_no_spak) nama_client, ");
			lSBquery.append("       a.mke_no_plat, ");
			lSBquery.append("       a.mke_no_rangka, ");
			lSBquery.append("       a.mke_no_mesin ");
			lSBquery.append("from mst_kendaraan a ");
			lSBquery.append("where 1=1 ");
			
			if (strNoPlat.trim().length() > 0) {
				lSBquery.append("and trim(a.mke_no_plat) = '"+strNoPlat+"' "); //'B8129VE' ");
			}
			
			if (strNoRangka.trim().length() > 0) {
				lSBquery.append("and trim(a.mke_no_rangka) = '"+strNoRangka+"' "); //'MHF11LF8000020658' ");
			}
			
			if (strNoMesin.trim().length() > 0) {
				lSBquery.append("and trim(a.mke_no_mesin) = '"+strNoMesin+"' "); //'2L9561290' ");
			}
					
			//BBApp_Common.fnPrint("[BBApp_Business] fnRetrieveDetailPeserta: " + lSBquery.toString());
			BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaPlatRangkaMesin) : "," " + lSBquery.toString());
			
			lPStmtFetch = lCnFetch.prepareStatement(lSBquery.toString());
			lRsFetch = lPStmtFetch.executeQuery();	
			
			while(lRsFetch.next()) {
				lObjBBApp_cjbBBAppResult.setStrNoPolis(BBApp_Common.fnGetValue(lRsFetch.getString("mds_no_polis")));
				lObjBBApp_cjbBBAppResult.setStrNama(BBApp_Common.fnGetValue(lRsFetch.getString("nama_client")));
				lObjBBApp_cjbBBAppResult.setStrNoPlat(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_plat")));
				lObjBBApp_cjbBBAppResult.setStrNoRangka(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_rangka")));
				lObjBBApp_cjbBBAppResult.setStrNoMesin(BBApp_Common.fnGetValue(lRsFetch.getString("mke_no_mesin")));
			}
			
			//lObjBBApp_cjbBBAppResult.setStrNoKartu(strNoKartu);
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			try
			{
				if (lRsFetch!=null) {lRsFetch.close(); lRsFetch=null;}
				if (lPStmtFetch!=null) {lPStmtFetch.close(); lPStmtFetch=null;}
				if (lCnFetch!=null) {lCnFetch.close(); lCnFetch=null;}
			}
			catch(Exception e) 
			{
				BBApp_Common.fnWriteLog("BBApp_Business", "BBApp_Business(fnRetDetPesertaPlatRangkaMesin) : ","Close Exception : "+e);
			}		
		}
		return lObjBBApp_cjbBBAppResult;
	}
}
